﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Mono.Data.Sqlite;

namespace DAL
{
    public class SQLiteHelper
    {
        //static string dbPath = @"C:\F\File\加密狗项目\PersonManager\PersonManager\DAL\bin\Debug\DB\PersonDataBase.db";
        //static string dbPath = @"F:\git_environment\csharp\winform01\DAL\DB\PersonDataBase.db";
        static string dbPath = @"C:\Users\Administrator\Desktop\c#\csharp\winform01\DAL\DB\PersonDataBase.db";

        /// <summary>
        ///执行数据库命令的方法，获取结果的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sp">SQL语句参数</param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql, params SqliteParameter[] sp)
        {
            using (SqliteConnection connection = new SqliteConnection("Data Source=" + dbPath))
            {
                //开启连接数据库
                connection.Open();
                //创建数据库命令对象
                SqliteCommand command = connection.CreateCommand();
                //SQL语句
                command.CommandText = sql;
                //添加参数
                command.Parameters.AddRange(sp);
                //执行SQL语句
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        /// <summary>
        /// 执行数据库命令的方法，获取查询的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sp">SQL语句参数</param>
        /// <returns></returns>
        public static List<List<object>> ExecuteReader(string sql, params SqliteParameter[] sp)
        {
            using (SqliteConnection connection = new SqliteConnection("Data Source=" + dbPath))
            {
                //开启连接数据库
                connection.Open();
                //创建数据库命令对象
                SqliteCommand command = connection.CreateCommand();
                //SQL语句
                command.CommandText = sql;
                //添加参数
                command.Parameters.AddRange(sp);
                //执行SQL语句
                SqliteDataReader reader = command.ExecuteReader();
                //声明数据列表
                List<List<object>> dataList = new List<List<object>>();
                //遍历结果集中的每一行数据
                int i = 0;
                while (reader.Read())
                {
                    dataList.Add(new List<object>());
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        dataList[i].Add(reader[j]);
                    }
                    i++;
                }
                //返回数据列表
                return dataList;
            }
        }

        public static List<List<Dictionary<String, object>>> ExecuteMap(string sql, params SqliteParameter[] sp)
        {
            using (SqliteConnection connection = new SqliteConnection("Data Source=" + dbPath))
            {
                //开启连接数据库
                connection.Open();
                //创建数据库命令对象
                SqliteCommand command = connection.CreateCommand();
                //SQL语句
                command.CommandText = sql;
                //添加参数
                command.Parameters.AddRange(sp);
                //执行SQL语句
                SqliteDataReader reader = command.ExecuteReader();
                //声明数据列表
                List<List<Dictionary<String, object>>> dataList = new List<List<Dictionary<String, object>>>();
                //遍历结果集中的每一行数据
                int i = 0;
                while (reader.Read())
                {
                    dataList.Add(new List<Dictionary<string, object>>());
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        Dictionary<String, object> keyValues = new Dictionary<string, object>();
                        keyValues.Add(reader.GetName(j), reader[j]);
                        dataList[i].Add(keyValues);
                    }
                    i++;
                }
                //返回数据列表
                return dataList;
            }
        }

        // 无参数sql执行
        public static List<List<Dictionary<String, object>>> ExecuteMap(string sql)
        {
            using (SqliteConnection connection = new SqliteConnection("Data Source=" + dbPath))
            {
                //开启连接数据库
                connection.Open();
                //创建数据库命令对象
                SqliteCommand command = connection.CreateCommand();
                //SQL语句
                command.CommandText = sql;
                //执行SQL语句
                SqliteDataReader reader = command.ExecuteReader();
                //声明数据列表
                List<List<Dictionary<String, object>>> dataList = new List<List<Dictionary<String, object>>>();
                //遍历结果集中的每一行数据
                int i = 0;
                while (reader.Read())
                {
                    dataList.Add(new List<Dictionary<string, object>>());
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        Dictionary<String, object> keyValues = new Dictionary<string, object>();
                        keyValues.Add(reader.GetName(j), reader[j]);
                        dataList[i].Add(keyValues);
                    }
                    i++;
                }
                //返回数据列表
                return dataList;
            }
        }

        /// <summary>
        /// 执行新增、修改、删除的操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sp">SQL语句参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqliteParameter[] sp)
        {
            using (SqliteConnection connection = new SqliteConnection("Data Source=" + dbPath))
            {
                //开启连接数据库
                connection.Open();
                //创建数据库命令对象
                SqliteCommand command = connection.CreateCommand();
                //SQL语句
                command.CommandText = sql;
                //添加参数
                command.Parameters.AddRange(sp);
                //执行SQL语句
                return command.ExecuteNonQuery();
            }
        }


        public static List<T> DataToModel<T>(List<List<Dictionary<string, object>>> data) where T : new()
        {
            List<T> list = new List<T>();
            data.ForEach(item =>
            {
                T temp = new T();
                Type type = temp.GetType();
                item.ForEach(dict =>
                {
                    foreach (KeyValuePair<string, object> kvp in dict)
                    {
                        PropertyInfo field = type.GetProperty(kvp.Key);
                        if (field == null)
                        {
                            continue;
                        }
                        if (field.PropertyType.Name.StartsWith("String"))
                        {
                            field.SetValue(temp, Convert.ToString(kvp.Value));
                        }
                        else
                        {
                            field.SetValue(temp, Convert.ToInt32(kvp.Value));
                        }

                    }
                });

                // 数据表格数据映射到MODEL
                list.Add(temp);
            });
            return list;
        }

    }
}
